import pyodbc, os, datetime

DETAILS = {
           'Severity': ['Integer', ''], 
           'Grout_Volume': ['Float', '.2f'], 
           'MHID': ['Integer', ''], 
           'Percentage': ['Integer', ''],
           'Continuous': ['Text', ''],
            'Distance_To': ['Float', '.2f'],
             'DetailID': ['Integer', '', 'PK'], 
             'RunID': ['Integer', '', 'FK'], 
             'Position_From': ['Text', ''], 
             'Joint_Number': ['Text', ''], 
             'Connection_Address': ['Text', ''], 
             'Dimension2': ['Text', ''], 
             'Intrusion': ['Float', '.2f'], 
             'Dimension1': ['Text', ''], 
             'ConnectionID': ['Integer', ''], 
             'Position_To': ['Text', ''], 
             'ModifierCode': ['Text', ''], 
             'Pass_Fail': ['Text', ''], 
             'Comment': ['Text', ''], 
             'Footage': ['Float', '.2f'], 
             'ConditionCode': ['Text', ''], 
             'VideoFrameNumber': ['Integer', ''], 
             'Measured_From_Up': ['Text', '']}




RUNS = {'Rim_to_Grade_(D)': ['Float', '.2f'], 
        'City': ['Text', ''], 
        'Upstream_MH': ['Text', ''], 
        'PO_Number': ['Text', ''], 
        'Rim_to_Invert_(D)': ['Float', '.2f'], 
        'Surveyor_Name': ['Text', ''], 
        'Downstream_MH': ['Text', ''], 
        'Structural_Grading': ['Text', ''], 
        'Pre-Cleaning': ['Text', ''], 
        'Year_Renewed': ['Integer', ''], 
        'Location_Details': ['Text', ''], 
        'Rim_to_Invert_(U)': ['Float', '.2f'], 
        'Grade_to_Invert_(U)': ['Float', '.2f'], 
        'Year_Laid': ['Integer', ''], 
        'Grade_to_Invert_(D)': ['Float', '.2f'], 
        'Pipe_Joint_Length': ['Float', '.2f'], 
        'Length_Surveyed': ['Float', '.2f'], 
        'Material': ['Text', ''], 
        'Direction_of_Survey': ['Text', ''], 
        'Additional_Info': ['Text', ''], 
        'DesignSlope': ['Float', '.2f'], 
        'Location_Code': ['Text', ''], 
        'Sewer_Use': ['Text', ''], 
        'RunID': ['Integer', '', 'PK'], 
        'Date': ['Text', ''], 
        'MPEGPath': ['Text', ''], 
        'DiscName': ['Text', ''], 
        'Purpose': ['Text', ''], 
        'Customer': ['Text', ''], 
        'Pipe_Segment_Reference': ['Text', ''], 
        'Sewer_Category': ['Text', ''], 
        'Weather': ['Text', ''], 
        'Certificate_Number': ['Text', ''], 
        'Time': ['Text', ''], 
#        'O_&_M_Grading': ['Text', ''], 
        'Date_Cleaned': ['Text', ''], 
        'Drainage_Area': ['Text', ''], 
        'Width': ['Integer', ''], 
        'Media_Label': ['Text', ''], 
        'EndFrame': ['Integer', ''], 
        'PIPETECHID': ['Text', ''], 
        'Shape': ['Text', ''], 
        'Street': ['Text', ''], 
        'Lining_Method': ['Text', ''], 
        'Flow_Control': ['Text', ''], 
        'Height_(Diameter)': ['Integer', ''], 
        'Owner': ['Text', ''], 
        'Total_Length': ['Float', '.2f'], 
#        'GIS_Map': ['Text', ''], 
        'Rim_to_Grade_(U)': ['Float', '.2f']}

RUNS_FLDS = RUNS.keys()
DETAILS_FLDS = DETAILS.keys()
FLDS = {'Runs': RUNS_FLDS, 'Details': DETAILS_FLDS}




class PipeTech(object):
    """
    A class for the PipeTech Database
    >>>env = r'c:\temp\CDM NWAC PACP.ptd'
    >>>password = 'sigchi'
    >>>p = PipeTech(env, password)
    >>>cursor = p.cursor
    >>>for x in cursor.execute('select * from Runs').fetchall():
    >>>    print x

    """
    def __init__(self,env,password):
        self.env = env.lower()
        self._connect(password)
    def _connect(self, password):
        conn_str = 'DRIVER={Microsoft Access Driver (*.mdb)};DBQ=%s;PWD=%s' % (os.path.normpath(self.env), password)
        cnxn = pyodbc.connect(conn_str)
        self.cursor = cnxn.cursor()
    def query(self, table, q):
        """
        return a list of runID that matchs the q
        >>>print p.query('1')#show everything
        >>>print p.query('RunID=1')
        """
        fld_list = FLDS[table]
        flds = '"' + '", "'.join(fld_list) + '"'
        sql = 'select %s from %s where %s' % (flds,table,q)
        runs = []
        return self.cursor.execute(sql).fetchall()



def date_converter(date, time): 
    """
    PipeTech use Date(text), Time(date/time) to record the date and time information.
    Which is really stupid.
    So I have to convert it into one field.
    """
    date = datetime.datetime.strptime(date, '%Y%m%d')
    new_date = datetime.datetime(date.year, date.month, date.day, time.hour, time.minute)
    return new_date

